To Load
Stg_StgHiring Table:
Source WorkerDetails:
SQL command Text:
SELECT [Work Order ID], [Current Bill Rate (DT/Hr)], [Current Bill Rate (OT/Hr)], [Current Bill Rate (ST/Hr)]
FROM [dbo].[Worker_Details]
WHERE [Work Order ID] IS NOT NULL
Columns Tab:
Source Timing:
SQL command Text:
select DISTINCT [Job Posting ID], [# Responses], [# Hired],
[# Positions Requested],[# Unfilled Positions], [Work Order ID], [Time to Fill]
from [dbo].[Timing]
where [Job Posting ID] is NOT null
Columns Tab:
Source SIH:
SQL command Text:
SELECT DISTINCT [SIH_Requisition ID], [SIH_# Interviewed], [SIH_# Accepted Interviews],[SIH_# Confirmed Interviews] FROM [dbo].[vw_SIH]
Columns Tab:
Sort:
Sort 1:
Sort 2:
Merge Join:
Sort 3:
Merge Join 1:
Derived Columns:
Derived Column Text:
GETDATE()
GETDATE()
ISNULL([Current Bill Rate (DT/Hr)]) ? 0 : [Current Bill Rate (DT/Hr)]
ISNULL([Current Bill Rate (OT/Hr)]) ? 0 : [Current Bill Rate (OT/Hr)]
ISNULL([Current Bill Rate (ST/Hr)]) ? 0 : [Current Bill Rate (ST/Hr)]
ISNULL([Job Posting ID]) ? "Unkown" : [Job Posting ID]
ISNULL([# Responses]) ? 0 : [# Responses]
ISNULL([# Hired]) ? 0 : [# Hired]
ISNULL([# Positions Requested]) ? 0 : [# Positions Requested]
ISNULL([# Unfilled Positions]) ? 0 : [# Unfilled Positions]
ISNULL([Time to Fill]) ? 0 : [Time to Fill]
ISNULL([Work Order ID (1)]) ? "Unkown" : [Work Order ID (1)]
ISNULL([SIH_# Interviewed]) ? 0 : [SIH_# Interviewed]
ISNULL([SIH_# Accepted Interviews]) ? 0 : [SIH_# Accepted Interviews]
ISNULL([SIH_# Confirmed Interviews]) ? 0 : [SIH_# Confirmed Interviews]
OLEDB Destination:
To Load_ReqCategory Table:
Source Vw_ReqA:
SQL command Text:
SELECT [ReqA_Requisition ID], [ReqA_Company Code],[ReqA_Category], [ReqA_Functional Org], [ReqA_Org], [ReqA_Division],
[ReqA_Subdivision], [ReqA_Level 3], [ReqA_Level 4], [ReqA_Level 5]
FROM [dbo].[vw_ReqA]
Column Tab:
Derived Column:
Derived Column Text:
GETDATE()
GETDATE()
ISNULL([ReqA_Functional Org]) ? "Unkown" : [ReqA_Functional Org]
ISNULL(ReqA_Org) ? "Unknown" : ReqA_Org
ISNULL(ReqA_Division) ? "Unknown" : ReqA_Division
ISNULL(ReqA_Subdivision) ? "Unknown" : ReqA_Subdivision
ISNULL([ReqA_Level 3]) ? "Unknown" : [ReqA_Level 3]
ISNULL([ReqA_Level 4]) ? " Unknown" : [ReqA_Level 4]
ISNULL([ReqA_Level 5]) ? "Unknown" : [ReqA_Level 5]
OLEDB Destination:
To Load_Stg_JobReq Table:
Source VW_ReqA:
Columns Tab:
Derived Column:
OLEDB Destination:
Mappings Tab:
To Load_StgWorker Table:
Source VW_Worker:
SQL command Text:
SELECT [Worker_Work Order ID], [Worker_Contractor ID], [Worker_Security ID], [Worker_Worker Type], [Worker_Contractor],
[Worker_Original Start Date], [Worker_Contractor Start Date], [Worker_Contractor End Date], [Worker_Contractor Closed Date],
[Worker_Contractor Status], [Worker_Contractor Type], [Worker_Main Document Type], [Worker_Nuetral Supplier Name],
[Worker_Diverse Supplier], [Worker_Supervisor],[Worker_Primary Company Code - Cost Center],[Worker_Template Title],[Worker_Country],
[Worker_City], [Worker_Site], [Worker_Address], [Worker_State/Province], [Worker_Uploaded?]
FROM [RecSta].[dbo].[vw_Worker]
where [Worker_Work Order ID] is not null
Columns Tab:
Data Conversion:
Derived Columns:
Derived Column Text:
GETDATE()
GETDATE()
ISNULL([Worker_Work Order ID]) ? "Unknown" : [Worker_Work Order ID]
ISNULL([Worker_Contractor ID]) ? "Unknown" : [Worker_Contractor ID]
ISNULL([Worker_Security ID]) ? "Unknown" : [Worker_Security ID]
ISNULL([Worker_Worker Type]) ? "Unknown" : [Worker_Worker Type]
ISNULL(Worker_Contractor) ? "Unknown" : Worker_Contractor
ISNULL([Worker_Contractor Status]) ? "Unknown" : [Worker_Contractor Status]
ISNULL([Worker_Contractor Type]) ? "Unknown" : [Worker_Contractor Type]
ISNULL([Worker_Main Document Type]) ? "Unknown" : [Worker_Main Document Type]
ISNULL([Worker_Nuetral Supplier Name]) ? "Unknown" : [Worker_Nuetral Supplier Name]
ISNULL([Worker_Diverse Supplier]) ? "Unknown" : [Worker_Diverse Supplier]
ISNULL(Worker_Supervisor) ? "Unknown" : Worker_Supervisor
ISNULL([Worker_Primary Company Code - Cost Center]) ? "" : [Worker_Primary Company Code - Cost Center]
ISNULL([Worker_Template Title]) ? "Unknown" : [Worker_Template Title]
ISNULL(Worker_City) ? "Unknown" : Worker_City
ISNULL(Worker_Country) ? "Unknown" : Worker_Country
ISNULL(Worker_Site) ? "Unknown" : Worker_Site
ISNULL([Worker_State/Province]) ? "Unknown" : [Worker_State/Province]
ISNULL([Worker_Uploaded?]) ? "Unknown" : [Worker_Uploaded?]
ISNULL(Worker_Address) ? "Unknown" : Worker_Address
ISNULL([Copy of Worker_Original Start Date]) ? "Unknown" : [Copy of Worker_Original Start Date]
ISNULL([Copy of Worker_Contractor Start Date]) ? "Unknown" : [Copy of Worker_Contractor Start Date]
ISNULL([Copy of Worker_Contractor End Date]) ? "Unknown" : [Copy of Worker_Contractor End Date]
ISNULL([Copy of Worker_Contractor Closed Date]) ? "Unknown" : [Copy of Worker_Contractor Closed Date]
Conditional split:
Conditional Split Text:
[Worker_State/Province] != "% %" || Worker_Address != "% %" || [Worker_Worker Type] != "% %"
Derived Columns:
Union All:
OLEDB Destination:
Mappings Tab:
Union All Transformation
https://www.tutorialgateway.org/union-all-transformation-in-ssis/